package com.goktech.project.hmw.dao.impl;

import com.goktech.project.hmw.dao.ICountyProjectDao;
import com.goktech.project.hmw.mapper.CountyProject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository
public class CountyProjectDaoImpl implements ICountyProjectDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	/**
	 * 区县总体人数金额
	 *
	 * @return
	 */
	@Override
	public List<CountyProject> listTotal() {
		String sqlStr = "SELECT county,SUM(TOTAL_CAPITAL)as TOTAL_CAPITAL ,SUM(TOTAL_PERSON)as TOTAL_PERSON FROM `all_in_one` GROUP BY county;";
		List<CountyProject> list = jdbcTemplate.query(sqlStr, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0) {
			return list;
		} else {
			return null;
		}
	}

	/**
	 * 区县按项目人数金额
	 *
	 * @return
	 */
	@Override
	public List<CountyProject> listProjectPersonCaption() {
		String sqlStr = "select county_person.grant_year as grant_year,county_person.county as county, total_person,TOTAL_CAPITAL,county_person.project_id from ( " +
				" (select grant_year,county,count(*) as total_person,project_id from county_project_person GROUP BY grant_year,county,project_id) as county_person " +
				" INNER JOIN " +
				" (select GRANT_YEAR,COUNTY,project_id, sum(TOTAL_CAPITAL) as TOTAL_CAPITAL from county_project_capital GROUP BY GRANT_YEAR,COUNTY,project_id) as county_capital " +
				" on county_person.county=county_capital.COUNTY " +
				" and county_person.grant_year=county_capital.GRANT_YEAR " +
				" and county_person.project_id=county_capital.project_id " +
				")";
		List<CountyProject> list = jdbcTemplate.query(sqlStr, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * 区县按时间人数金额
	 *
	 * @return
	 */
	@Override
	public List<CountyProject> listYearPersonCaption() {
		String sqlStr = "select county_person.grant_year as grant_year,county_person.county as county, total_person,TOTAL_CAPITAL from ( " +
				" (select grant_year,county,count(*) as total_person from county_project_person GROUP BY grant_year,county) as county_person " +
				" inner JOIN  " +
				" (select GRANT_YEAR,COUNTY,sum(TOTAL_CAPITAL) as TOTAL_CAPITAL from county_project_capital GROUP BY GRANT_YEAR,COUNTY) as county_capital " +
				" on county_person.county=county_capital.COUNTY and county_person.grant_year=county_capital.GRANT_YEAR " +
				" )";
		List<CountyProject> list = jdbcTemplate.query(sqlStr, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * 区县人数项目人均发放
	 *
	 * @return
	 */
	@Override
	public List<CountyProject> listAvlProjectPersonCaption() {
		String sqlStr = "select county_person.grant_year as grant_year,county_person.county as county, total_person,TOTAL_CAPITAL,county_person.project_id, convert(TOTAL_CAPITAL/total_person,decimal(32,2)) as avl_caption from ( " +
				" (select grant_year,county,count(*) as total_person,project_id from county_project_person GROUP BY grant_year,county,project_id) as county_person " +
				" INNER JOIN " +
				" (select GRANT_YEAR,COUNTY,project_id, sum(TOTAL_CAPITAL) as TOTAL_CAPITAL from county_project_capital GROUP BY GRANT_YEAR,COUNTY,project_id) as county_capital " +
				" on county_person.county=county_capital.COUNTY " +
				" and county_person.grant_year=county_capital.GRANT_YEAR " +
				" and county_person.project_id=county_capital.project_id " +
				")";
		List<CountyProject> list = jdbcTemplate.query(sqlStr, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * 前十
	 *
	 * @param year
	 * @return
	 */
	@Override
	public List<CountyProject> listTop10(String year) {
		String sqlStr = "select county_person.grant_year as grant_year,county_person.county as county, total_person,TOTAL_CAPITAL from ( " +
				" (select grant_year,county,count(*) as total_person from county_project_person GROUP BY grant_year,county) as county_person " +
				" inner JOIN " +
				" (select GRANT_YEAR,COUNTY,sum(TOTAL_CAPITAL) as TOTAL_CAPITAL from county_project_capital GROUP BY GRANT_YEAR,COUNTY) as county_capital " +
				" on county_person.county=county_capital.COUNTY and county_person.grant_year=county_capital.GRANT_YEAR " +
				" ) where county_person.grant_year= ? order by county_capital.TOTAL_CAPITAL desc ,county_person.total_person DESC limit 10";
		List<CountyProject> list = jdbcTemplate.query(sqlStr, new Object[]{year}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * 福州市总金额人数逐年统计
	 *
	 * @return
	 */
	@Override
	public List<CountyProject> listCityTotalCapitalPerson() {
		String sqlStr = " select sum(total_person) as total_person,sum(total_capital) as total_capital,grant_year from ( " +
				" select county_person.grant_year as grant_year,county_person.county as county, total_person,TOTAL_CAPITAL from ( " +
				" (select grant_year,county,count(*) as total_person from county_project_person GROUP BY grant_year,county) as county_person " +
				" inner JOIN " +
				" (select GRANT_YEAR,COUNTY,sum(TOTAL_CAPITAL) as TOTAL_CAPITAL from county_project_capital GROUP BY GRANT_YEAR,COUNTY) as county_capital " +
				" on county_person.county=county_capital.COUNTY and county_person.grant_year=county_capital.GRANT_YEAR )) as total " +
				" group by grant_year " +
				" order by grant_year ";
		List<CountyProject> list = jdbcTemplate.query(sqlStr, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * 通过区县名称获取区县人数和金额按照时间纬度
	 *
	 * @return
	 */
	@Override
	public List<CountyProject> listPersonCapitalYearByCounty(String county) {
		String sqlStr = "select sum(total_person) as total_person,sum(total_capital) as total_capital,county,grant_year as grant_year from ( " +
				" select county_person.grant_year as grant_year,county_person.county as county, total_person,TOTAL_CAPITAL from ( " +
				" (select grant_year,county,count(*) as total_person from county_project_person GROUP BY grant_year,county) as county_person " +
				" INNER JOIN " +
				" (select GRANT_YEAR,COUNTY,sum(TOTAL_CAPITAL) as TOTAL_CAPITAL from county_project_capital GROUP BY GRANT_YEAR,COUNTY) as county_capital " +
				" on county_person.county=county_capital.COUNTY and county_person.grant_year=county_capital.GRANT_YEAR )) as total " +
				" where county= ? " +
				" group by county,grant_year " +
				" order by grant_year ";
		List<CountyProject> list = jdbcTemplate.query(sqlStr, new Object[]{county}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * 通过区县获取区县人数项目人均发放 前top old
	 * @param county
	 * @param top
	 * @return
	 */
	@Override
	public List<CountyProject> listAvlProjectPersonCaptionByCounty(String county, String top) {
		String sqlStr = String.format(" select county_person.grant_year as grant_year,county_person.county as county, total_person,TOTAL_CAPITAL,county_person.project_id, convert(TOTAL_CAPITAL/total_person,decimal(32,2)) as avl_caption from ( " +
				" (select grant_year,county,count(*) as total_person,project_id from county_project_person GROUP BY grant_year,county,project_id) as county_person " +
				" INNER JOIN " +
				" (select GRANT_YEAR,COUNTY,project_id, sum(TOTAL_CAPITAL) as TOTAL_CAPITAL from county_project_capital GROUP BY GRANT_YEAR,COUNTY,project_id) as county_capital " +
				" on county_person.county=county_capital.COUNTY " +
				" and county_person.grant_year=county_capital.GRANT_YEAR " +
				" and county_person.project_id=county_capital.project_id " +
				")  where county_person.county= '%s' order by avl_caption desc LIMIT %s; ",county,top);
		List<CountyProject> list = jdbcTemplate.query(sqlStr, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * 通过区县获取下一级数据
	 * @param county
	 * @return
	 */
	@Override
	public List<CountyProject> listTownshipTotal(String county){
		String sqlStr=" select county_person.COUNTY,county_person.TOWNSHIP,TOTAL_PERSON,TOTAL_CAPITAL from " +
				" (select COUNTY,TOWNSHIP,sum(TOTAL_PERSON) TOTAL_PERSON from county_township_person GROUP BY COUNTY,TOWNSHIP) as county_person " +
				" inner join " +
				"  (select COUNTY,TOWNSHIP,sum(TOTAL_CAPITAL) TOTAL_CAPITAL from county_project_capital GROUP BY COUNTY,TOWNSHIP) as county_capital " +
				" on county_person.COUNTY=county_capital.COUNTY and " +
				" county_person.TOWNSHIP=county_capital.TOWNSHIP " +
				" where county_person.COUNTY= ?";
		List<CountyProject> list = jdbcTemplate.query(sqlStr, new Object[]{county}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * 区县按项目人数金额TOP
	 * @param column 排序列
	 * @param top 前几
	 * @return
	 */
	@Override
	public List<CountyProject> listProjectPersonCaptionTop(String column,String top) {
		String sqlStr = String.format("select county_person.grant_year as grant_year,county_person.county as county, total_person,TOTAL_CAPITAL,county_person.project_id from ( " +
				" (select grant_year,county,count(*) as total_person,project_id from county_project_person GROUP BY grant_year,county,project_id) as county_person " +
				" INNER JOIN " +
				" (select GRANT_YEAR,COUNTY,project_id, sum(TOTAL_CAPITAL) as TOTAL_CAPITAL from county_project_capital GROUP BY GRANT_YEAR,COUNTY,project_id) as county_capital " +
				" on county_person.county=county_capital.COUNTY " +
				" and county_person.grant_year=county_capital.GRANT_YEAR " +
				" and county_person.project_id=county_capital.project_id " +
				") ORDER BY %s desc LIMIT %s; ",column,top);
		List<CountyProject> list = jdbcTemplate.query(sqlStr, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * 按项目地区获取前top
	 * @param type
	 * @param top
	 * @return
	 */
	@Override
	public List<CountyProject> listRight2(String type,String top){
		List<CountyProject> list;
		String sqlStr;
		if(type.equals("pro")) {//项目
			sqlStr =String.format(" select TOTAL_CAPITAL,a.PROJECT_ID,total_person,convert(TOTAL_CAPITAL/citycount/total_person,DECIMAL(32,2)) avl_caption from " +
					" (select sum(TOTAL_CAPITAL) TOTAL_CAPITAL,PROJECT_ID from county_project_capital group by PROJECT_ID) as a " +
					" LEFT JOIN " +
					" (select count(*) citycount,PROJECT_ID from ( select DISTINCT county,PROJECT_ID from county_project_capital GROUP BY COUNTY,PROJECT_ID) as county group by PROJECT_ID ) b " +
					" on a.PROJECT_ID=b.PROJECT_ID " +
					" left join  " +
					" (select count(*) total_person,project_id from county_project_person group by project_id) as c " +
					" on b.PROJECT_ID=c.project_id " +
					" order by avl_caption desc LIMIT %s;",top);
		}else{//地区
			sqlStr=String.format(" select b.county,total_person,TOTAL_CAPITAL, convert(TOTAL_CAPITAL/projcount/total_person,DECIMAL(32,2)) avl_caption from " +
					" (select count(*) total_person,county from county_project_person GROUP BY county) a " +
					" RIGHT JOIN " +
					" (select sum(TOTAL_CAPITAL) TOTAL_CAPITAL,COUNTY from county_project_capital group by COUNTY) b " +
					" on a.county=b.COUNTY " +
					" LEFT JOIN " +
					" (select count(*) projcount,county from (select DISTINCT county,PROJECT_ID from county_project_capital GROUP BY COUNTY,PROJECT_ID) as proj group by county) c " +
					" on b.COUNTY=c.county " +
					" order by avl_caption desc LIMIT %s;",top);
		}
		list = jdbcTemplate.query(sqlStr, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * 通过区县获取区县人数项目人均发放前top当前年
	 * @param county
	 * @param top
	 * @return
	 */
	@Override
	public List<CountyProject> listAvlProjectPersonCaptionTopByCounty(String county, String top) {
		String sqlStr = String.format("  select * from  " +
				" ( " +
				"  SELECT county,project_id,count(*) as total_person,GRANT_YEAR,sum(convert(GRANT_CAPITAL,DECIMAL(32,2))) total_capital,convert(sum(convert(GRANT_CAPITAL,DECIMAL(32,2)))/count(*),DECIMAL(32,2)) as avl_caption,GRANT_TIME from county_project_month  " +
				"  where county= '"+ county +"' and GRANT_TIME='1' " +
				"  GROUP BY county,project_id,GRANT_YEAR,GRANT_TIME  " +
				"  order by avl_caption desc LIMIT "+top+
				" ) a union all " +
				" select * from ( " +
				"  SELECT county,project_id,count(*) as total_person,GRANT_YEAR,sum(convert(GRANT_CAPITAL,DECIMAL(32,2))) total_capital,convert(sum(convert(GRANT_CAPITAL,DECIMAL(32,2)))/count(*),DECIMAL(32,2)) as avl_caption,GRANT_TIME from county_project_month  " +
				"  where county='"+ county +"' and GRANT_TIME='2' " +
				"  GROUP BY county,project_id,GRANT_YEAR,GRANT_TIME  " +
				"  order by avl_caption desc LIMIT "+top+
				" ) b union all " +
				" select * from ( " +
				"  SELECT county,project_id,count(*) as total_person,GRANT_YEAR,sum(convert(GRANT_CAPITAL,DECIMAL(32,2))) total_capital,convert(sum(convert(GRANT_CAPITAL,DECIMAL(32,2)))/count(*),DECIMAL(32,2)) as avl_caption,GRANT_TIME from county_project_month  " +
				"  where county='"+ county +"' and GRANT_TIME='3' " +
				"  GROUP BY county,project_id,GRANT_YEAR,GRANT_TIME  " +
				"  order by avl_caption desc LIMIT "+top+
				" ) c union all " +
				" select * from ( " +
				"  SELECT county,project_id,count(*) as total_person,GRANT_YEAR,sum(convert(GRANT_CAPITAL,DECIMAL(32,2))) total_capital,convert(sum(convert(GRANT_CAPITAL,DECIMAL(32,2)))/count(*),DECIMAL(32,2)) as avl_caption,GRANT_TIME from county_project_month  " +
				"  where county='"+ county +"' and GRANT_TIME='4' " +
				"  GROUP BY county,project_id,GRANT_YEAR,GRANT_TIME  " +
				"  order by avl_caption desc LIMIT "+top+
				" ) d union all " +
				" select * from ( " +
				"  SELECT county,project_id,count(*) as total_person,GRANT_YEAR,sum(convert(GRANT_CAPITAL,DECIMAL(32,2))) total_capital,convert(sum(convert(GRANT_CAPITAL,DECIMAL(32,2)))/count(*),DECIMAL(32,2)) as avl_caption,GRANT_TIME from county_project_month  " +
				"  where county='"+ county +"' and GRANT_TIME='5' " +
				"  GROUP BY county,project_id,GRANT_YEAR,GRANT_TIME  " +
				"  order by avl_caption desc LIMIT "+top+
				" ) e ; ");
		List<CountyProject> list = jdbcTemplate.query(sqlStr, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * 右1
	 * @param column 排序列
	 * @param top 前几
	 * @return
	 */
	@Override
	public List<CountyProject> listRight1(String column,String top) {
		String sqlStr="";
		if(column.equals("county")){
			sqlStr=" select * from ( " +
					" select sum(TOTAL_CAPITAL) as TOTAL_CAPITAL,sum(total_person) total_person,county from " +
					" (select county_person.grant_year as grant_year,county_person.county as county, total_person,TOTAL_CAPITAL,county_person.project_id from ( " +
					" (select grant_year,county,count(*) as total_person,project_id from county_project_person GROUP BY grant_year,county,project_id) as county_person " +
					" INNER JOIN " +
					" (select GRANT_YEAR,COUNTY,project_id, sum(TOTAL_CAPITAL) as TOTAL_CAPITAL from county_project_capital GROUP BY GRANT_YEAR,COUNTY,project_id) as county_capital " +
					" on county_person.county=county_capital.COUNTY " +
					" and county_person.grant_year=county_capital.GRANT_YEAR " +
					" and county_person.project_id=county_capital.project_id " +
					" )) as t1 group by county ORDER BY TOTAL_CAPITAL desc limit "+top+" ) as a " +
					" union all " +
					" select * from ( " +
					" select sum(TOTAL_CAPITAL) as TOTAL_CAPITAL,sum(total_person) total_person,county from " +
					" (select county_person.grant_year as grant_year,county_person.county as county, total_person,TOTAL_CAPITAL,county_person.project_id from ( " +
					" (select grant_year,county,count(*) as total_person,project_id from county_project_person GROUP BY grant_year,county,project_id) as county_person " +
					" INNER JOIN " +
					" (select GRANT_YEAR,COUNTY,project_id, sum(TOTAL_CAPITAL) as TOTAL_CAPITAL from county_project_capital GROUP BY GRANT_YEAR,COUNTY,project_id) as county_capital " +
					" on county_person.county=county_capital.COUNTY " +
					" and county_person.grant_year=county_capital.GRANT_YEAR " +
					" and county_person.project_id=county_capital.project_id " +
					" )) as t2 group by county ORDER BY total_person desc limit "+top+" ) as b ";
		}
		if(column.equals("pro")){
			sqlStr=" select * from ( " +
					" select sum(TOTAL_CAPITAL) as TOTAL_CAPITAL,sum(total_person) total_person,project_id from " +
					" (select county_person.grant_year as grant_year,county_person.county as county, total_person,TOTAL_CAPITAL,county_person.project_id from ( " +
					" (select grant_year,county,count(*) as total_person,project_id from county_project_person GROUP BY grant_year,county,project_id) as county_person " +
					" INNER JOIN " +
					" (select GRANT_YEAR,COUNTY,project_id, sum(TOTAL_CAPITAL) as TOTAL_CAPITAL from county_project_capital GROUP BY GRANT_YEAR,COUNTY,project_id) as county_capital " +
					" on county_person.county=county_capital.COUNTY " +
					" and county_person.grant_year=county_capital.GRANT_YEAR " +
					" and county_person.project_id=county_capital.project_id " +
					" )) as t1 group by project_id ORDER BY TOTAL_CAPITAL desc limit 5) as a " +
					" union all " +
					" select * from ( " +
					" select sum(TOTAL_CAPITAL) as TOTAL_CAPITAL,sum(total_person) total_person,project_id from " +
					" (select county_person.grant_year as grant_year,county_person.county as county, total_person,TOTAL_CAPITAL,county_person.project_id from ( " +
					" (select grant_year,county,count(*) as total_person,project_id from county_project_person GROUP BY grant_year,county,project_id) as county_person " +
					" INNER JOIN " +
					" (select GRANT_YEAR,COUNTY,project_id, sum(TOTAL_CAPITAL) as TOTAL_CAPITAL from county_project_capital GROUP BY GRANT_YEAR,COUNTY,project_id) as county_capital " +
					" on county_person.county=county_capital.COUNTY " +
					" and county_person.grant_year=county_capital.GRANT_YEAR " +
					" and county_person.project_id=county_capital.project_id " +
					" )) as t2 group by project_id ORDER BY total_person desc limit "+top+") as b";
		}
		List<CountyProject> list = jdbcTemplate.query(sqlStr, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	/**
	 * 获取项目的总金额总人数项目参与的总区县
	 * @return
     */
	@Override
	public List<CountyProject> allListProject() {
//		String str="SELECT TOTAL_CAPITAL,a.PROJECT_ID,total_person,d.total_project FROM" +
//				"(SELECT sum(TOTAL_CAPITAL) TOTAL_CAPITAL,PROJECT_ID FROM county_project_capital where  PROJECT_ID in ('城乡低保补助','特困人员补助(五保)','农业支持保护补贴','生态林补偿金','中央财政扶贫资金','福州扶贫济困春风行动','农村危房改造补助','造福工程补助','扶贫小额信贷贴息','被征地农民养老保障金','普通高中国家助学金','工会金秋助学金')GROUP BY PROJECT_ID) AS a" +
//				" LEFT JOIN " +
//				"(SELECT count(*) total_person,project_id FROM county_project_person where  PROJECT_ID in ('城乡低保补助','特困人员补助(五保)','农业支持保护补贴','生态林补偿金','中央财政扶贫资金','福州扶贫济困春风行动','农村危房改造补助','造福工程补助','扶贫小额信贷贴息','被征地农民养老保障金','普通高中国家助学金','工会金秋助学金')GROUP BY project_id)AS b" +
//				" ON a.PROJECT_ID = b.project_id " +
//				"LEFT JOIN " +
//				"(SELECT PROJECT_ID,count(*) total_project FROM(SELECT DISTINCT county,PROJECT_ID FROM county_project_capital where grant_year='2018'and PROJECT_ID in ('城乡低保补助','特困人员补助(五保)','农业支持保护补贴','生态林补偿金','中央财政扶贫资金','福州扶贫济困春风行动','农村危房改造补助','造福工程补助','扶贫小额信贷贴息','被征地农民养老保障金','普通高中国家助学金','工会金秋助学金')GROUP BY COUNTY,PROJECT_ID)c GROUP BY PROJECT_ID)d" +
//				" ON d.PROJECT_ID = a.PROJECT_ID order by field(a.PROJECT_ID,'城乡低保补助','特困人员补助(五保)','农业支持保护补贴','生态林补偿金','中央财政扶贫资金','福州扶贫济困春风行动','农村危房改造补助','造福工程补助','扶贫小额信贷贴息','被征地农民养老保障金','普通高中国家助学金','工会金秋助学金')";
//
		String str="SELECT project_id,SUM(TOTAL_CAPITAL)TOTAL_CAPITAL,sum(TOTAL_PERSON) as TOTAL_PERSON FROM `all_in_one`  where project_id in \n" +
				"('城乡低保补助','特困人员补助(五保)','农业支持保护补贴','生态林补偿金','中央财政扶贫资金','福州扶贫济困春风行动',\n" +
				"'农村危房改造补助','造福工程补助','扶贫小额信贷贴息','被征地农民养老保障金','普通高中国家助学金','工会金秋助学金')GROUP BY project_id\n" +
				"order by field(PROJECT_ID,'城乡低保补助','特困人员补助(五保)','农业支持保护补贴','生态林补偿金','中央财政扶贫资金','福州扶贫济困春风行动','农村危房改造补助','造福工程补助','扶贫小额信贷贴息','被征地农民养老保障金','普通高中国家助学金','工会金秋助学金')";
		String str2="SELECT project_id,SUM(TOTAL_CAPITAL)TOTAL_CAPITAL,sum(TOTAL_PERSON) as TOTAL_PERSON FROM `all_in_one`  where project_id NOT in \n" +
				"('城乡低保补助','特困人员补助(五保)','农业支持保护补贴','生态林补偿金','中央财政扶贫资金','福州扶贫济困春风行动',\n" +
				"'农村危房改造补助','造福工程补助','扶贫小额信贷贴息','被征地农民养老保障金','普通高中国家助学金','工会金秋助学金')GROUP BY project_id\n" +
				"order by  TOTAL_CAPITAL DESC";
//		String str2="SELECT a.TOTAL_CAPITAL,a.PROJECT_ID,b.total_person,d.total_project FROM" +
//				"(SELECT sum(TOTAL_CAPITAL)TOTAL_CAPITAL,PROJECT_ID FROM county_project_capital where  PROJECT_ID not in ('城乡低保补助','特困人员补助(五保)','农业支持保护补贴','生态林补偿金','中央财政扶贫资金','福州扶贫济困春风行动','农村危房改造补助','造福工程补助','扶贫小额信贷贴息','被征地农民养老保障金','普通高中国家助学金','工会金秋助学金')GROUP BY PROJECT_ID)AS a" +
//				" LEFT JOIN" +
//				"(SELECT count(*) total_person,project_id FROM county_project_person where  PROJECT_ID not in ('城乡低保补助','特困人员补助(五保)','农业支持保护补贴','生态林补偿金','中央财政扶贫资金','福州扶贫济困春风行动','农村危房改造补助','造福工程补助','扶贫小额信贷贴息','被征地农民养老保障金','普通高中国家助学金','工会金秋助学金')GROUP BY project_id)AS b" +
//				" ON a.PROJECT_ID = b.project_id " +
//				"LEFT JOIN" +
//				"(SELECT PROJECT_ID,count(*) total_project FROM(SELECT DISTINCT county,PROJECT_ID FROM county_project_capital where  PROJECT_ID not in ('城乡低保补助','特困人员补助(五保)','农业支持保护补贴','生态林补偿金','中央财政扶贫资金','福州扶贫济困春风行动','农村危房改造补助','造福工程补助','扶贫小额信贷贴息','被征地农民养老保障金','普通高中国家助学金','工会金秋助学金')GROUP BY COUNTY,PROJECT_ID)c GROUP BY PROJECT_ID)d" +
//				" ON d.PROJECT_ID = a.PROJECT_ID order by d.total_project DESC ";
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		List<CountyProject> list2 = jdbcTemplate.query(str2, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));

		if (list != null && list.size() > 0 ) {
			for (int i = 0; i < list2.size(); i++) {
				if(!"金秋助学金".equals(list2.get(i).getProject_id())){
					list.add(list2.get(i));
				}

			}
			return list;
		}
		else {
			return null;
		}
	}

	/**
	 * 根据地区名获取项目人均总发放金额总人数 项目地区镇乡总个数（点击右一）
	 * @param
     * @return
     */
	@Override
	public List<CountyProject> allListCounty(Map<String,String> reqMap) {
		String county=reqMap.get("county");
		String year=new SimpleDateFormat("yyyy").format(new Date());
//		String str="SELECT a.county,a.PROJECT_ID,b.TOTAL_CAPITAL AS TOTAL_CAPITAL,a.total_person,c.TOTAL_TOWNSHIP,CONVERT(b.TOTAL_CAPITAL / a.total_person,DECIMAL (32, 2)) AS avl_caption FROM" +
//				"(SELECT count(*) total_person,county,PROJECT_ID FROM county_project_person " +
//				//"where grant_year='"+year+"'" +
//				" GROUP BY county,PROJECT_ID) a " +
//				"INNER JOIN " +
//				"(SELECT sum(TOTAL_CAPITAL) TOTAL_CAPITAL,COUNTY,PROJECT_ID FROM county_project_capital " +
//				//" where grant_year='"+year+"' " +
//				" GROUP BY COUNTY,PROJECT_ID) b " +
//				"ON a.county = b.county AND a.PROJECT_ID = b.PROJECT_ID " +
//				"LEFT JOIN" +
//				"(select county,PROJECT_ID,count(1)as TOTAL_TOWNSHIP from " +
//				"(SELECT county,TOWNSHIP,PROJECT_ID  FROM `county_project_capital` " +
//				"GROUP BY county,TOWNSHIP,PROJECT_ID)d GROUP BY d.county,d.PROJECT_ID)c " +
//				"ON c.county=a.county and c.PROJECT_ID=a.PROJECT_ID " +
//				"HAVING county ='"+county+"' " ;
//				"ORDER BY total_person DESC";

		String str="select a.county,a.PROJECT_ID,a.TOTAL_CAPITAL,a.total_person,d.TOTAL_TOWNSHIP,  CONVERT (a.TOTAL_CAPITAL / a.total_person,DECIMAL (32, 2)) AS avl_caption from (SELECT county,PROJECT_ID,SUM(TOTAL_CAPITAL) TOTAL_CAPITAL,SUM(total_person) total_person FROM `all_in_one` " +
				"WHERE county='"+county+"' GROUP BY county,PROJECT_ID) a " +
				"LEFT JOIN " +
				"(SELECT b.county,project_id ,count(1) TOTAL_TOWNSHIP from (SELECT county,township,project_id from all_in_one GROUP BY county,township,project_id)b GROUP BY b.county,b.project_id)d ON a.county=d.county and d.project_id=a.PROJECT_ID " ;

		if(reqMap.get("type")!=""&& reqMap.get("type")!=null){
			int type =Integer.parseInt(reqMap.get("type"));
			String or;
			if(type>0){
				or="desc";

			}else{
				or="asc";
			}
			type=Math.abs(type);
			if(1==type){
				str+=" ORDER BY TOTAL_TOWNSHIP "+or;
			}else if(2==type){
				str+=" ORDER BY total_person "+or ;
			} else if (3==type) {
				str += " ORDER BY total_capital "+or;
			}
		}else{
			str+="ORDER BY total_person DESC";
		}
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	/**
	 * 所有区县人均发放金额（当前年）
	 * @return
     */
	@Override
	public List<CountyProject> avlAllListCounty() {
//		String year=new SimpleDateFormat("yyyy").format(new Date());
//		String str="select a.county,b.TOTAL_PERSON,a.TOTAL_CAPITAL,CONVERT (a.TOTAL_CAPITAL / b.total_person,DECIMAL (32, 2)) AS avl_caption from " +
//				"(SELECT county,sum(TOTAL_CAPITAL)TOTAL_CAPITAL FROM county_project_capital " +
//
//				" GROUP BY county)a " +
//				"INNER JOIN" +
//				"(SELECT county,count(*)TOTAL_PERSON from county_project_person " +
//
//				" GROUP BY county) b " +
//				"ON a.county=b.county ORDER BY avl_caption desc";
		String str="select a.county,a.TOTAL_CAPITAL,a.total_person,  CONVERT (a.TOTAL_CAPITAL / a.total_person,DECIMAL (32, 2)) AS avl_caption from (SELECT county,SUM(TOTAL_CAPITAL) TOTAL_CAPITAL,SUM(total_person) total_person FROM `all_in_one` GROUP BY county) a ORDER BY avl_caption desc";
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	/**
	 * 根据项目名获取项目人均总发放金额总人数 项目地区镇乡总个数（点击右一）
     * @return
     */
	@Override
	public List<CountyProject> pitchListProject(Map<String,String> reqMap) {
		String year=new SimpleDateFormat("yyyy").format(new Date());

//		if("城乡低保补助".equals(reqMap.get("project")) || "特困人员补助(五保)".equals(reqMap.get("project")) ){
//			str += "SELECT a.county,a.PROJECT_ID,b.TOTAL_CAPITAL AS TOTAL_CAPITAL,a.total_person,c.TOTAL_TOWNSHIP,CONVERT(b.TOTAL_CAPITAL / a.total_person,DECIMAL (32, 2)) AS avl_caption FROM" +
//					"(SELECT count(*) total_person,county,PROJECT_ID FROM county_project_person3 " +
//					//"where grant_year in('2017')" +
//					" GROUP BY county,PROJECT_ID) a " +
//					"INNER JOIN " +
//					"(SELECT sum(TOTAL_CAPITAL) TOTAL_CAPITAL,COUNTY,PROJECT_ID FROM county_project_capital " +
//					//" where grant_year in('2017')" +
//					" GROUP BY COUNTY,PROJECT_ID) b " +
//					"ON a.county = b.county AND a.PROJECT_ID = b.PROJECT_ID " +
//					"LEFT JOIN" +
//					"(select PROJECT_ID,county,count(*)TOTAL_TOWNSHIP from county_project_capital " +
//					" where grant_year='" + year + "'" +
//					" GROUP BY PROJECT_ID,county)c " +
//					"ON c.county=a.county and c.PROJECT_ID=a.PROJECT_ID " +
//					"HAVING PROJECT_ID ='" + reqMap.get("project") + "'";
//		}else {

//			str += "SELECT a.county,a.PROJECT_ID,b.TOTAL_CAPITAL AS TOTAL_CAPITAL,a.total_person,c.TOTAL_TOWNSHIP,CONVERT(b.TOTAL_CAPITAL / a.total_person,DECIMAL (32, 2)) AS avl_caption FROM" +
//					"(SELECT count(*) total_person,county,PROJECT_ID FROM county_project_person " +
//					//"where grant_year in('2017')" +
//					" GROUP BY county,PROJECT_ID) a " +
//					"INNER JOIN " +
//					"(SELECT sum(TOTAL_CAPITAL) TOTAL_CAPITAL,COUNTY,PROJECT_ID FROM county_project_capital " +
//					//" where grant_year in('2017')" +
//					" GROUP BY COUNTY,PROJECT_ID) b " +
//					"ON a.county = b.county AND a.PROJECT_ID = b.PROJECT_ID " +
//					"LEFT JOIN" +
//					"(select county,PROJECT_ID, count(1)as TOTAL_TOWNSHIP from " +
//					"(select county,TOWNSHIP,PROJECT_ID from county_project_capital GROUP BY county,TOWNSHIP,PROJECT_ID ) d " +
//					"GROUP BY d.county,d.PROJECT_ID)c " +
//					"ON c.county=a.county and c.PROJECT_ID=a.PROJECT_ID " +
//					"HAVING PROJECT_ID ='" + reqMap.get("project") + "'";
//				" ORDER BY total_person DESC";
//		}
		String str="select a.county,a.PROJECT_ID,a.TOTAL_CAPITAL,a.total_person,d.TOTAL_TOWNSHIP,  CONVERT (a.TOTAL_CAPITAL / a.total_person,DECIMAL (32, 2)) AS avl_caption from (SELECT county,PROJECT_ID,SUM(TOTAL_CAPITAL) TOTAL_CAPITAL,SUM(total_person) total_person FROM `all_in_one` WHERE project_id='"+reqMap.get("project")+"' GROUP BY county,PROJECT_ID) a  " +
				"LEFT JOIN " +
				"(SELECT b.county,project_id ,count(1) TOTAL_TOWNSHIP from (SELECT county,township,project_id from all_in_one GROUP BY county,township,project_id)b GROUP BY b.county,b.project_id)d ON a.county=d.county and d.project_id=a.PROJECT_ID ";
		if(reqMap.get("type")!=""&&reqMap.get("type")!=null) {
			int type = Integer.parseInt(reqMap.get("type"));
			String or;
			if(type>0){
				or="desc";

			}else{
				or="asc";
			}
			type=Math.abs(type);
			if (1==type) {
				str += " ORDER BY TOTAL_TOWNSHIP "+or;
			} else if (2==type) {
				str += " ORDER BY total_person "+or;
			} else if (3==type) {
				str += " ORDER BY TOTAL_CAPITAL "+or;
			}

		}
		else{
				str+=" ORDER BY total_person DESC ";
		}
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	@Override
	public List<CountyProject> yearList(String top) {
		String year=new SimpleDateFormat("yyyy").format(new Date());
		String str="select * from " +
				"(select a.GRANT_YEAR,a.TOTAL_CAPITAL,b.TOTAL_PERSON from " +
				"(SELECT GRANT_YEAR,sum(TOTAL_CAPITAL) AS TOTAL_CAPITAL FROM county_project_capital GROUP BY GRANT_YEAR )a " +
				"LEFT JOIN " +
				"(select GRANT_YEAR,count(*)TOTAL_PERSON from county_project_person GROUP BY GRANT_YEAR)b " +
				"on a.GRANT_YEAR=b.GRANT_YEAR HAVING GRANT_YEAR<='"+year+"' ORDER BY GRANT_YEAR desc  LIMIT "+top+" )c" +
				" ORDER BY GRANT_YEAR asc";

		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	@Override
	public List<CountyProject> townshipList(String county) {
		String year=new SimpleDateFormat("yyyy").format(new Date());
//		String str="select a.county,a.TOWNSHIP,a.TOTAL_CAPITAL,b.TOTAL_PERSON FROM" +
//				"(SELECT county,TOWNSHIP,sum(TOTAL_CAPITAL)as TOTAL_CAPITAL FROM county_project_capital   where " +
//				" county='"+county+"' GROUP BY county,TOWNSHIP)a " +
//				"LEFT JOIN" +
//				"(select county,TOWNSHIP,sum(TOTAL_pERson)as TOTAL_PERSON from county_township_person GROUP BY county,TOWNSHIP)b " +
//				"ON a.county=b.county and a.TOWNSHIP=b.TOWNSHIP ";
		String str="SELECT county,TOWNSHIP,SUM(TOTAL_CAPITAL)TOTAL_CAPITAL ,SUM(total_person)total_person from  `all_in_one` " +
				"where county='"+county+"'  GROUP BY county,TOWNSHIP";
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	@Override
	public List<CountyProject> topCountyList(String county,String top) {
		String year=new SimpleDateFormat("yyyy").format(new Date());
//		String str="select a.county,a.PROJECT_ID,a.TOTAL_CAPITAL,b.TOTAL_PERSON,CONVERT(a.TOTAL_CAPITAL / b.total_person,DECIMAL (32, 2)) AS avl_caption from " +
//				"(select county,PROJECT_ID,SUM(TOTAL_CAPITAL)TOTAL_CAPITAL from county_project_capital " +
//				//"where GRANT_YEAR='"+year+"'" +
//				" GROUP BY COUNTY,PROJECT_ID)a " +
//				"LEFT JOIN" +
//				"(select county,PROJECT_ID,count(*)TOTAL_PERSON from county_project_person " +
//				//" where GRANT_YEAR='"+year+"'" +
//				" GROUP BY county,PROJECT_ID)b " +
//				"on a.county=b.county and a.PROJECT_ID=b.PROJECT_ID  HAVING county='"+county+"' ORDER BY avl_caption desc LIMIT "+top;
		String str="select a.county,a.PROJECT_ID,a.TOTAL_CAPITAL,a.total_person,CONVERT (a.TOTAL_CAPITAL / a.total_person,DECIMAL (32, 2)) AS avl_caption from (SELECT county,PROJECT_ID,SUM(TOTAL_CAPITAL) TOTAL_CAPITAL,SUM(total_person) total_person FROM`all_in_one`" +
				" where  county='"+county+"' GROUP BY county,PROJECT_ID)a  ORDER BY avl_caption desc LIMIT "+top;
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	@Override
	public List<CountyProject> toppitchListProject(String project,String top) {
		String year=new SimpleDateFormat("yyyy").format(new Date());
//		String str="select a.county,a.PROJECT_ID,a.TOTAL_CAPITAL,b.TOTAL_PERSON,CONVERT(a.TOTAL_CAPITAL / b.total_person,DECIMAL (32, 2)) AS avl_caption from " +
//				"(select county,PROJECT_ID,SUM(TOTAL_CAPITAL)TOTAL_CAPITAL from county_project_capital " +
//				//" where GRANT_YEAR='"+year+"'" +
//				" GROUP BY COUNTY,PROJECT_ID)a " +
//				"LEFT JOIN" +
//				"(select county,PROJECT_ID,count(*)TOTAL_PERSON from county_project_person " +
//				//"where GRANT_YEAR='"+year+"'" +
//				"GROUP BY county,PROJECT_ID)b " +
//				"on a.county=b.county and a.PROJECT_ID=b.PROJECT_ID  HAVING PROJECT_ID='"+project+"' ORDER BY avl_caption desc LIMIT "+top;
		String str="select a.county,a.PROJECT_ID,a.TOTAL_CAPITAL,a.total_person,CONVERT (a.TOTAL_CAPITAL / a.total_person,DECIMAL (32, 2)) AS avl_caption from " +
				"(SELECT county,PROJECT_ID,SUM(TOTAL_CAPITAL) TOTAL_CAPITAL,SUM(total_person) total_person FROM`all_in_one` " +
				"where  PROJECT_ID='"+project+"' GROUP BY county,PROJECT_ID)a  ORDER BY avl_caption desc limit "+top;
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	@Override
	public List<CountyProject> topYearList(String project,String top) {
		String year=new SimpleDateFormat("yyyy").format(new Date());
		String str="select * from " +
				"(select a.GRANT_YEAR,a.PROJECT_ID,a.TOTAL_CAPITAL,b.TOTAL_PERSON from " +
				"(SELECT GRANT_YEAR,PROJECT_ID,sum(TOTAL_CAPITAL) AS TOTAL_CAPITAL FROM county_project_capital GROUP BY GRANT_YEAR,PROJECT_ID )a" +
				" LEFT JOIN" +
				"(select GRANT_YEAR,project_id,count(*)TOTAL_PERSON from county_project_person GROUP BY GRANT_YEAR,project_id)b " +
				"on a.GRANT_YEAR=b.GRANT_YEAR and a.PROJECT_ID=b.project_id HAVING GRANT_YEAR<='"+year+"' and project_id='"+project+"' ORDER BY GRANT_YEAR desc  LIMIT "+top+" )c " +
				"ORDER BY GRANT_YEAR asc";

		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	@Override
	public List<CountyProject> topTotalProjectList(String project) {
		String year=new SimpleDateFormat("yyyy").format(new Date());
		//" where GRANT_YEAR='"+year+"'" +
//		String str="select a.county,a.PROJECT_ID,a.TOTAL_CAPITAL,b.total_person from " +
//				"(select county ,PROJECT_ID,SUM(TOTAL_CAPITAL)TOTAL_CAPITAL from county_project_capital" +
//				" GROUP BY county,PROJECT_ID)a" +
//				" LEFT JOIN(select county ,project_id,count(*)total_person from county_project_person" +
//				" GROUP BY county,project_id)b " +
//				"on a.county=b.county and a.PROJECT_ID=b.project_id having PROJECT_ID='"+project+"' ";
		String str="SELECT county,PROJECT_ID,SUM(TOTAL_CAPITAL)TOTAL_CAPITAL ,SUM(total_person)total_person from  `all_in_one`\n" +
				"GROUP BY county,PROJECT_ID having PROJECT_ID='"+project+"'";
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	@Override
	public List<CountyProject> topTotalCountyList(String county, String top) {
		String year=new SimpleDateFormat("yyyy").format(new Date());
		String str="select * from " +
				"(select a.GRANT_YEAR,a.county,a.TOTAL_CAPITAL,b.TOTAL_PERSON from " +
				"(SELECT GRANT_YEAR,county,sum(TOTAL_CAPITAL) AS TOTAL_CAPITAL FROM county_project_capital GROUP BY GRANT_YEAR,county )a" +
				" LEFT JOIN" +
				"(select GRANT_YEAR,county,count(*)TOTAL_PERSON from county_project_person GROUP BY GRANT_YEAR,county)b " +
				"on a.GRANT_YEAR=b.GRANT_YEAR and a.county=b.county HAVING GRANT_YEAR<='"+year+"' and county='"+county+"' ORDER BY GRANT_YEAR desc  LIMIT "+top+" )c " +
				"ORDER BY GRANT_YEAR asc";

		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	@Override
	public List<CountyProject> totalTownshipList(String county, String project) {
		String year=new SimpleDateFormat("yyyy").format(new Date());
		String str="SELECT a.county,a.PROJECT_ID,a.TOWNSHIP,a.TOTAL_CAPITAL,c.TOTAL_PERSON,e.total_village, CONVERT(a.TOTAL_CAPITAL / c.total_person,DECIMAL (32, 2)) AS avl_caption FROM " +
				"(select county ,PROJECT_ID,TOWNSHIP,SUM(TOTAL_CAPITAL)TOTAL_CAPITAL from county_project_capital GROUP BY county ,PROJECT_ID,TOWNSHIP)a " +
				"LEFT JOIN" +
				"(select county ,PROJECT_ID,TOWNSHIP ,count(*)TOTAL_PERSON from " +
				"(select name, county ,PROJECT_ID,TOWNSHIP from sj" +
//				" where GRANT_YEAR='"+year+"'" +
				" )b GROUP BY county ,PROJECT_ID,TOWNSHIP )c " +
				"ON A.county=C.county AND A.PROJECT_ID=c.PROJECT_ID and a.TOWNSHIP=c.TOWNSHIP " +
				"LEFT JOIN " +
				"(select d.county,d.TOWNSHIP ,d.PROJECT_ID,count(*)total_village from  " +
				"(select county,TOWNSHIP ,VILLAGE,PROJECT_ID from sj GROUP BY county,TOWNSHIP,VILLAGE,PROJECT_ID  )d" +
				"  GROUP BY d.county,d.TOWNSHIP ,d.PROJECT_ID)e on e.county=a.county and e.TOWNSHIP=a.TOWNSHIP and e.PROJECT_ID=a.PROJECT_ID" +
				" having county='"+county+"' and PROJECT_ID='"+project+"'";

		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	@Override
	public List<CountyProject> indexAmplification(String year) {

		String str="SELECT gt.grant_year,gt.grant_month,ifnull(temp.total_person,0)total_person ,ifnull(temp.total_capital,0)total_capital FROM grant_time gt left join (SELECT lt.count_year AS grant_year,lt.count_month AS grant_month,SUM(lt.total_person) total_person,SUM(lt.total_capital) total_capital FROM left_two lt " +
				"WHERE lt.count_year IN ('2016', '2017')GROUP BY lt.count_year,lt.count_month)temp on temp.grant_month = gt.grant_month and temp.grant_year = gt.grant_year ORDER BY ABS(gt.grant_year),ABS(gt.grant_month) ASC";
//		for (int i=1;i<3;i++ ){
//			String years=Integer.valueOf(year)-i+"";
//			if(i==2){
//				str+="'"+years+"'";
//			}else{
//				str+="'"+years+"',";
//			}
//		}
//		str+=")GROUP BY lt.count_year,lt.count_month)temp on temp.grant_month = gt.grant_month and temp.grant_year = gt.grant_year ORDER BY ABS(gt.grant_year),ABS(gt.grant_month) ASC";
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	@Override
	public List<CountyProject> amplificationCounty(String year, String county) {

		String str="SELECT gt.grant_year,gt.grant_month,ifnull(temp.total_person,0)total_person,ifnull(temp.total_capital,0)total_capital FROM grant_time gt left join (SELECT lt.count_year AS grant_year,lt.count_month AS grant_month,SUM(lt.total_person) total_person,SUM(lt.total_capital) total_capital FROM left_two lt " +
				"WHERE lt.count_year IN ('2016', '2017')AND lt.county = '"+county+"'GROUP BY lt.count_year,lt.count_month)temp on temp.grant_month = gt.grant_month and temp.grant_year = gt.grant_year ORDER BY ABS(gt.grant_year),ABS(gt.grant_month) ASC";
//		for (int i=1;i<3;i++ ){
//			String years=Integer.valueOf(year)-i+"";
//			if(i==2){
//				str+="'"+years+"'";
//			}else{
//				str+="'"+years+"',";
//			}
//		}
//		str+=")AND lt.county = '"+county+"'GROUP BY lt.count_year,lt.count_month)temp on temp.grant_month = gt.grant_month and temp.grant_year = gt.grant_year ORDER BY ABS(gt.grant_year),ABS(gt.grant_month) ASC";
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	@Override
	public List<CountyProject> amplificationProject(String year, String project) {
		String str="SELECT gt.grant_year,gt.grant_month,ifnull(temp.total_person,0) total_person,ifnull(temp.total_capital,0 ) total_capital FROM grant_time gt left join (SELECT lt.count_year AS grant_year,lt.count_month AS grant_month,SUM(lt.total_person) total_person,SUM(lt.total_capital) total_capital FROM left_two lt" +
				" WHERE lt.count_year IN ('2016', '2017')AND lt.PROJECT_ID = '"+project+"'GROUP BY lt.count_year,lt.count_month)temp on temp.grant_month = gt.grant_month and temp.grant_year = gt.grant_year ORDER BY ABS(gt.grant_year),ABS(gt.grant_month) ASC";
//		for (int i=1;i<3;i++ ){
//			String years=Integer.valueOf(year)-i+"";
//			if(i==2){
//				str+="'"+years+"'";
//			}else{
//				str+="'"+years+"',";
//			}
//		}
//		str+=")AND lt.PROJECT_ID = '"+project+"'GROUP BY lt.count_year,lt.count_month)temp on temp.grant_month = gt.grant_month and temp.grant_year = gt.grant_year ORDER BY ABS(gt.grant_year),ABS(gt.grant_month) ASC";
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}


//	@Override
//	public List<CountyProject> townMap(Map<String, String> reqMap) {
//		String str="SELECT a.county,a.project_id,a.township,a.TOTAL_PERSON,a.TOTAL_CAPITAL,b.PRECISION,b.latitude FROM" +
//				"(SELECT COUNTY,TOWNSHIP,PROJECT_ID,COUNT(1) AS TOTAL_PERSON,SUM(GRANT_CAPITAL) AS TOTAL_CAPITAL FROM county_project_detail where grant_year='2017'and  PROJECT_ID = '"+reqMap.get("project")+"' " +
//				"GROUP BY county,project_id,township ";
////		String str="SELECT a.county,a.project_id,a.township,d.TOTAL_PERSON,a.TOTAL_CAPITAL,b.`PRECISION`,b.latitude FROM" +
////				"(SELECT COUNTY,TOWNSHIP,PROJECT_ID,SUM(GRANT_CAPITAL) AS TOTAL_CAPITAL FROM county_project_detail " +
////				"where PROJECT_ID =  '"+reqMap.get("project")+"' GROUP BY county,project_id,township  ) a " +
////				"LEFT JOIN (SELECT * FROM city) b ON a.township = b.`NAME` LEFT JOIN(select county,township,count(1)TOTAL_PERSON from " +
////				"(select county ,township,`NAME` ,idcard from county_project_detail " +
////				"where PROJECT_ID =  '"+reqMap.get("project")+"' GROUP BY county ,township,`NAME` ,idcard)c " +
////				"GROUP BY county,township ) d on a.township = d.township ";
////		}if(!StringUtils.isEmpty(reqMap.get("county"))){
////			str+="and county='"+reqMap.get("county")+"'";
////
//		str+=" ) a LEFT JOIN (SELECT * FROM city) b ON a.township = b. NAME";
//		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
//		if (list != null && list.size() > 0 )
//			return list;
//		else
//			return null;
//	}
@Override
public List<CountyProject> townMap(Map<String, String> reqMap) {
//	String str="SELECT a.county,a.project_id,a.township,a.TOTAL_PERSON,a.TOTAL_CAPITAL,b.PRECISION,b.latitude FROM" +
//			"(SELECT COUNTY,TOWNSHIP,PROJECT_ID,COUNT(1) AS TOTAL_PERSON,SUM(GRANT_CAPITAL) AS TOTAL_CAPITAL FROM county_project_detail where grant_year='2017' " +
//			"GROUP BY county,project_id,township HAVING PROJECT_ID = '"+reqMap.get("project")+"' ";
	StringBuilder sb = new StringBuilder(" ");
	sb.append(" SELECT ");
	sb.append(" a.county, ");
	sb.append(" a.project_id, ");
	sb.append(" a.township, ");
	sb.append(" d.TOTAL_PERSON, ");
	sb.append(" a.TOTAL_CAPITAL, ");
	sb.append(" b.`PRECISION`, ");
	sb.append(" b.latitude ");
	sb.append(" FROM ");
	sb.append(" ( ");
	sb.append(" SELECT ");
	sb.append(" COUNTY, ");
	sb.append(" TOWNSHIP, ");
	sb.append(" PROJECT_ID, ");
	sb.append(" TOTAL_CAPITAL as TOTAL_CAPITAL ");
	sb.append(" FROM ");
	sb.append(" county_township_captial  ");
	sb.append(" WHERE ");
	sb.append(" PROJECT_ID = '"+reqMap.get("project")+"' ");
	sb.append(" ");
	sb.append(" ) a ");
	sb.append(" LEFT JOIN (SELECT * FROM city) b ON a.township = b.`NAME` ");
	sb.append(" LEFT JOIN ( ");
	sb.append(" SELECT ");
	sb.append(" ctp.COUNTY, ");
	sb.append(" ctp.TOWNSHIP, ");
	sb.append(" ctp.PROJECT_ID, ");
	sb.append(" ctp.person_count as TOTAL_PERSON ");
	sb.append(" FROM ");
	sb.append(" county_township_personcount ctp ");
	sb.append(" WHERE ");
	sb.append(" ctp.PROJECT_ID = '"+reqMap.get("project")+"' ");
	sb.append(" ) d ON a.township = d.township ");

	List<CountyProject> list = jdbcTemplate.query(sb.toString(), new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
	if (list != null && list.size() > 0 )
		return list;
	else
		return null;
}

	@Override
	public List<CountyProject> allproject() {
		String str="SELECT a.TOTAL_CAPITAL,a.PROJECT_ID,b.total_person,d.total_project FROM(SELECT sum(TOTAL_CAPITAL) TOTAL_CAPITAL,PROJECT_ID FROM county_project_capital WHERE grant_year = '2017'GROUP BY PROJECT_ID)AS a LEFT JOIN (SELECT count(*) total_person,project_id FROM county_project_person WHERE grant_year = '2017' GROUP BY project_id)AS b ON a.PROJECT_ID = b.project_id LEFT JOIN (SELECT PROJECT_ID,count(*) total_project FROM(SELECT DISTINCT county,PROJECT_ID FROM county_project_capital WHERE grant_year = '2017' GROUP BY COUNTY,PROJECT_ID)c GROUP BY PROJECT_ID) d ON d.PROJECT_ID = a.PROJECT_ID ORDER BY d.total_project DESC";
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	@Override
	public List<CountyProject> zongCounty() {
		String str="select DISTINCT county from  county_project_capital";
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{},new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}

	@Override
	public List<CountyProject> zongProject() {
		String str="select DISTINCT project_id from  county_project_capital";
		List<CountyProject> list = jdbcTemplate.query(str, new Object[]{}, new BeanPropertyRowMapper<>(CountyProject.class));
		if (list != null && list.size() > 0 )
			return list;
		else
			return null;
	}
}
